﻿



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[sp_WebPlus_StaffUser_Select]
    @username VARCHAR(255),
    @oAuth bit = 0
AS
-- ===============================================================
-- Author:		Andrew Breward
-- Create date: AndyB 15/01/2023 
-- Modified:    AndyB 20/05/2024 removed TOP 1 in order to cater for Staff with multiple PermissionGroupUser records e.g. Standard,Administrator
-- Modified:	Chris 09/12/25 Add oAuth flag to make it look at User.[OAuthUserName]
-- Descripton:	SP to retrieve the User details (joined to Staff and Permissions tables)
-- Example:     exec sp_WebPlus_UserStaff_Select 'AndyB'
-- ===============================================================



if(@username is null or LTRIM(RTRIM(@username)) = '' )
	BEGIN
		RAISERROR('Username is required.', 16, 1);
        RETURN;
	END


	SELECT 
		u.UserID, u.Title, u.Username, u.Forenames, u.Surname, u.EmailAddress, u.Mobiletelephone, u.Enabled AS UserEnabled, u.Password, u.PermissionGroupTypeID, 
		s.StaffID, s.StaffCode, s.EmailAddress AS StaffEmail, s.Enabled AS StaffEnabled,
		pgt.PermissionGroupTypeName, 
		pgu.PermissionGroupUserID, pgu.PermissionGroupID, 
		pg.PermissionGroupName
    FROM
        [User] u
    LEFT OUTER JOIN
		[Staff] s ON u.UserID = s.UserID AND s.Enabled = 1   -- this is to make sure the staff record exists and is enabled
	LEFT OUTER JOIN
		[PermissionGroupType] pgt ON u.PermissionGroupTypeID = pgt.PermissionGroupTypeID
	LEFT OUTER JOIN
		[PermissionGroupUser] pgu ON u.UserID = pgu.UserID
	LEFT OUTER JOIN
		[PermissionGroup] pg ON pgu.PermissionGroupID= pg.PermissionGroupID
    WHERE
		--(u.username = @username and @oAuth = 0) or (u.OAuthUserName = @username and @oAuth = 1)
		        @username = CASE 
                        WHEN @oAuth = 1 THEN u.OAuthUserName 
                        ELSE u.Username 
                    END
	ORDER BY 
	    pgu.PermissionGroupUserID DESC;  -- so we get the latest if > 1 
GO




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER PROCEDURE [dbo].[sp_WebPlus_VAStudent_Update]
@StaffId        INTEGER,
@EstactVAID     INTEGER,
@PersonalTargetGrade   VARCHAR(255),
@Point1_Grade   VARCHAR(20),
@Point2_Grade   VARCHAR(20), 
@Point3_Grade   VARCHAR(20), 
@Point4_Grade   VARCHAR(20), 
@Point5_Grade   VARCHAR(20), 
@Point6_Grade   VARCHAR(20), 
@Point7_Grade   VARCHAR(20), 
@Point8_Grade   VARCHAR(20), 
@Point9_Grade   VARCHAR(20), 
@Point10_Grade  VARCHAR(20), 
@Point11_Grade  VARCHAR(20), 
@Point12_Grade  VARCHAR(20), 
@Point1_Notes   VARCHAR(1024),
@Point2_Notes   VARCHAR(1024),
@Point3_Notes   VARCHAR(1024),
@Point4_Notes   VARCHAR(1024),
@Point5_Notes   VARCHAR(1024),
@Point6_Notes   VARCHAR(1024),
@Point7_Notes   VARCHAR(1024),
@Point8_Notes   VARCHAR(1024),
@Point9_Notes   VARCHAR(1024),
@Point10_Notes  VARCHAR(1024),
@Point11_Notes  VARCHAR(1024),
@Point12_Notes  VARCHAR(1024),
@Point1_EffortID INT,
@Point2_EffortID INT,
@Point3_EffortID INT,
@Point4_EffortID INT,
@Point5_EffortID INT,
@Point6_EffortID INT,
@Point7_EffortID INT,
@Point8_EffortID INT,
@Point9_EffortID INT,
@Point10_EffortID INT,
@Point11_EffortID INT,
@Point12_EffortID INT

AS
-- =============================================
-- Author:		Andrew Breward
-- Created:     AndyB 2/2/2023 
-- Modified:	AndyB 23/09/2023 @PersonalTargetGrade_Existing is now VARCHAR(255)
--              AndyB 11/10/2023 Create AuditTrail entries for changed values
--              AndyB 15/01/2024 Changed AuditChange value to either INSERT or UPDATE
--              AndyB 10/04/2024 v1.37.2 Bugfix: write UserName instead of StaffCode (join on UserID)
--				ChrisC 06/03/26 v1.41 Bug fix looks at OAuth when in OAuth mode for username
--
-- Descripton:	SP to Update the EstactVA (Grades and Notes) values
-- Example:     exec sp_WebPlus_VAStudent_Update
-- =============================================

DECLARE @newID          INT;
DECLARE @InYearGradeID  INT;
DECLARE @AcademicYearID VARCHAR(5);
DECLARE @LearningAimRef VARCHAR(8);
DECLARE @StudentID      INT;
DECLARE @EnrolmentID    INT;
DECLARE @CourseID       INT;
DECLARE @OriginalCourseID INT;

DECLARE @UserName       VARCHAR(255);
DECLARE @UserForenames  VARCHAR(100);
DECLARE @UserSurname    VARCHAR(100);

DECLARE @RowDescription VARCHAR(500);

DECLARE @Forenames      VARCHAR(100);
DECLARE @Surname        VARCHAR(100);
DECLARE @CourseCode     VARCHAR(50);

DECLARE @Point1_Grade_Existing VARCHAR(20);
DECLARE @Point2_Grade_Existing VARCHAR(20);
DECLARE @Point3_Grade_Existing VARCHAR(20);
DECLARE @Point4_Grade_Existing VARCHAR(20);
DECLARE @Point5_Grade_Existing VARCHAR(20);
DECLARE @Point6_Grade_Existing VARCHAR(20);
DECLARE @Point7_Grade_Existing VARCHAR(20);
DECLARE @Point8_Grade_Existing VARCHAR(20);
DECLARE @Point9_Grade_Existing VARCHAR(20);
DECLARE @Point10_Grade_Existing VARCHAR(20);
DECLARE @Point11_Grade_Existing VARCHAR(20);
DECLARE @Point12_Grade_Existing VARCHAR(20);

DECLARE @Point1_Notes_Existing VARCHAR(1024);
DECLARE @Point2_Notes_Existing VARCHAR(1024);
DECLARE @Point3_Notes_Existing VARCHAR(1024);
DECLARE @Point4_Notes_Existing VARCHAR(1024);
DECLARE @Point5_Notes_Existing VARCHAR(1024);
DECLARE @Point6_Notes_Existing VARCHAR(1024);
DECLARE @Point7_Notes_Existing VARCHAR(1024);
DECLARE @Point8_Notes_Existing VARCHAR(1024);
DECLARE @Point9_Notes_Existing VARCHAR(1024);
DECLARE @Point10_Notes_Existing VARCHAR(1024);
DECLARE @Point11_Notes_Existing VARCHAR(1024);
DECLARE @Point12_Notes_Existing VARCHAR(1024);

DECLARE @Point1_EffortDescription_Existing VARCHAR(100);
DECLARE @Point2_EffortDescription_Existing VARCHAR(100);
DECLARE @Point3_EffortDescription_Existing VARCHAR(100);
DECLARE @Point4_EffortDescription_Existing VARCHAR(100);
DECLARE @Point5_EffortDescription_Existing VARCHAR(100);
DECLARE @Point6_EffortDescription_Existing VARCHAR(100);
DECLARE @Point7_EffortDescription_Existing VARCHAR(100);
DECLARE @Point8_EffortDescription_Existing VARCHAR(100);
DECLARE @Point9_EffortDescription_Existing VARCHAR(100);
DECLARE @Point10_EffortDescription_Existing VARCHAR(100);
DECLARE @Point11_EffortDescription_Existing VARCHAR(100);
DECLARE @Point12_EffortDescription_Existing VARCHAR(100);

DECLARE @Point1_EffortDescription_New VARCHAR(100);
DECLARE @Point2_EffortDescription_New VARCHAR(100);
DECLARE @Point3_EffortDescription_New VARCHAR(100);
DECLARE @Point4_EffortDescription_New VARCHAR(100);
DECLARE @Point5_EffortDescription_New VARCHAR(100);
DECLARE @Point6_EffortDescription_New VARCHAR(100);
DECLARE @Point7_EffortDescription_New VARCHAR(100);
DECLARE @Point8_EffortDescription_New VARCHAR(100);
DECLARE @Point9_EffortDescription_New VARCHAR(100);
DECLARE @Point10_EffortDescription_New VARCHAR(100);
DECLARE @Point11_EffortDescription_New VARCHAR(100);
DECLARE @Point12_EffortDescription_New VARCHAR(100);

DECLARE @Point1_EffortID_Existing INT;
DECLARE @Point2_EffortID_Existing INT;
DECLARE @Point3_EffortID_Existing INT;
DECLARE @Point4_EffortID_Existing INT;
DECLARE @Point5_EffortID_Existing INT;
DECLARE @Point6_EffortID_Existing INT;
DECLARE @Point7_EffortID_Existing INT;
DECLARE @Point8_EffortID_Existing INT;
DECLARE @Point9_EffortID_Existing INT;
DECLARE @Point10_EffortID_Existing INT;
DECLARE @Point11_EffortID_Existing INT;
DECLARE @Point12_EffortID_Existing INT;

DECLARE @PersonalTargetGrade_Existing VARCHAR(255);

DECLARE @TypeOfChange VARCHAR(50);

-- ***********************************************************
--  Check if the Student ID already exists
-- ***********************************************************
IF EXISTS (SELECT 1 FROM EstActVA WHERE EstactVA.ID = @EstactVAID)
BEGIN
    Print 'EstactVAID Exists';

	-- ***********************************************************
	--  Get the details from the main table: EstactVA
	-- ***********************************************************
	SELECT 
		@AcademicYearID = EstactVA.AcademicYearID,
		@Forenames = ISNULL(EstactVA.Forenames, ''),
		@Surname = ISNULL(EstactVA.Surname, ''),
		@CourseCode = ISNULL(EstactVA.CourseCode, ''),
		@StudentID = Enrolment.StudentID,
		@EnrolmentID = Enrolment.ID,
		@LearningAimRef = Enrolment.LearningAimRef,
		@CourseID = Enrolment.CourseID,
		@OriginalCourseID = OGP_Enrolment.OriginalCourseID,
		@PersonalTargetGrade_Existing = ISNULL(EstactVA.PersonalTargetGrade, ''),
--
		@Point1_Grade_Existing = ISNULL(EstactVA.Point1_Grade, ''),
		@Point2_Grade_Existing = ISNULL(EstactVA.Point2_Grade, ''),
		@Point3_Grade_Existing = ISNULL(EstactVA.Point3_Grade, ''),
		@Point4_Grade_Existing = ISNULL(EstactVA.Point4_Grade, ''),
		@Point5_Grade_Existing = ISNULL(EstactVA.Point5_Grade, ''),
		@Point6_Grade_Existing = ISNULL(EstactVA.Point6_Grade, ''),
		@Point7_Grade_Existing = ISNULL(EstactVA.Point7_Grade, ''),
		@Point8_Grade_Existing = ISNULL(EstactVA.Point8_Grade, ''),
		@Point9_Grade_Existing = ISNULL(EstactVA.Point9_Grade, ''),
		@Point10_Grade_Existing = ISNULL(EstactVA.Point10_Grade, ''),
		@Point11_Grade_Existing = ISNULL(EstactVA.Point11_Grade, ''),
		@Point12_Grade_Existing = ISNULL(EstactVA.Point12_Grade, ''),
--		
		@Point1_Notes_Existing = ISNULL(EstactVA.Point1_Notes, ''),
		@Point2_Notes_Existing = ISNULL(EstactVA.Point2_Notes, ''),
		@Point3_Notes_Existing = ISNULL(EstactVA.Point3_Notes, ''),
		@Point4_Notes_Existing = ISNULL(EstactVA.Point4_Notes, ''),
		@Point5_Notes_Existing = ISNULL(EstactVA.Point5_Notes, ''),
		@Point6_Notes_Existing = ISNULL(EstactVA.Point6_Notes, ''),
		@Point7_Notes_Existing = ISNULL(EstactVA.Point7_Notes, ''),
		@Point8_Notes_Existing = ISNULL(EstactVA.Point8_Notes, ''),
		@Point9_Notes_Existing = ISNULL(EstactVA.Point9_Notes, ''),
		@Point10_Notes_Existing = ISNULL(EstactVA.Point10_Notes, ''),
		@Point11_Notes_Existing = ISNULL(EstactVA.Point11_Notes, ''),
		@Point12_Notes_Existing = ISNULL(EstactVA.Point12_Notes, ''),
--
		@Point1_EffortDescription_Existing = ISNULL(EstActVA.Point1_EffortDescription, ''),
		@Point2_EffortDescription_Existing = ISNULL(EstActVA.Point2_EffortDescription, ''),
		@Point3_EffortDescription_Existing = ISNULL(EstActVA.Point3_EffortDescription, ''),
		@Point4_EffortDescription_Existing = ISNULL(EstActVA.Point4_EffortDescription, ''),
		@Point5_EffortDescription_Existing = ISNULL(EstActVA.Point5_EffortDescription, ''),
		@Point6_EffortDescription_Existing = ISNULL(EstActVA.Point6_EffortDescription, ''),
		@Point7_EffortDescription_Existing = ISNULL(EstActVA.Point7_EffortDescription, ''),
		@Point8_EffortDescription_Existing = ISNULL(EstActVA.Point8_EffortDescription, ''),
		@Point9_EffortDescription_Existing = ISNULL(EstActVA.Point9_EffortDescription, ''),
		@Point10_EffortDescription_Existing = ISNULL(EstActVA.Point10_EffortDescription, ''),
		@Point11_EffortDescription_Existing = ISNULL(EstActVA.Point11_EffortDescription, ''),
		@Point12_EffortDescription_Existing = ISNULL(EstActVA.Point12_EffortDescription, '')
	 FROM 
		EstactVA 
			INNER JOIN Enrolment ON Enrolment.ID = EstactVA.ID
				INNER JOIN OGP_Enrolment  ON OGP_Enrolment.StudentRef = Enrolment.StudentRef AND 
				                             OGP_Enrolment.AcademicYearID = Enrolment.AcademicYearID AND
											 OGP_Enrolment.CourseID = Enrolment.CourseID
	 WHERE 
		EstactVA.ID = @EstactVAID; 


	-- ***********************************************************
	--  Get the EffortGrade Description from the ID
	-- ***********************************************************
	SELECT @Point1_EffortDescription_New = '';
	SELECT @Point2_EffortDescription_New = '';
	SELECT @Point3_EffortDescription_New = '';
	SELECT @Point4_EffortDescription_New = '';
	SELECT @Point5_EffortDescription_New = '';
	SELECT @Point6_EffortDescription_New = '';
	SELECT @Point7_EffortDescription_New = '';
	SELECT @Point8_EffortDescription_New = '';
	SELECT @Point9_EffortDescription_New = '';
	SELECT @Point10_EffortDescription_New = '';
	SELECT @Point11_EffortDescription_New = '';
	SELECT @Point12_EffortDescription_New = '';

	SELECT @Point1_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point1_EffortID, 0);
	SELECT @Point2_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point2_EffortID, 0);
	SELECT @Point3_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point3_EffortID, 0);
	SELECT @Point4_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point4_EffortID, 0);
	SELECT @Point5_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point5_EffortID, 0);
	SELECT @Point6_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point6_EffortID, 0);
	SELECT @Point7_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point7_EffortID, 0);
	SELECT @Point8_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point8_EffortID, 0);
	SELECT @Point9_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point9_EffortID, 0);
	SELECT @Point10_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point10_EffortID, 0);
	SELECT @Point11_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point11_EffortID, 0);
	SELECT @Point12_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point12_EffortID, 0);


	-- ***********************************************************
	--  Get the Staff Username
	-- ***********************************************************
	--SELECT @UserName = StaffCode FROM Staff WHERE StaffID = @StaffId;
	--SELECT 
	--	@UserName = [User].UserName,
	--	@UserForenames = [User].Forenames,
	--	@UserSurname = [User].Surname
	--FROM Staff 
	--	INNER JOIN [User] ON [User].UserID = Staff.UserID
	--WHERE 
	--    StaffID = @StaffId;

	
	DECLARE @SysValueValue VARCHAR(255)
    SELECT @SysValueValue = SystemSettingValue.SystemSettingValue FROM SystemSettingValue WHERE SystemSettingValue.SystemSettingName = 'OneGradePlusAuthenticationType'
	
	IF @SysValueValue = 'OAUTH'
	BEGIN
		SELECT 
			@UserName = [User].OAuthUserName,
			@UserForenames = [User].Forenames,
			@UserSurname = [User].Surname
		FROM Staff 
			INNER JOIN [User] ON [User].UserID = Staff.UserID
		WHERE 
			StaffID = @StaffId
	END
	ELSE
	BEGIN
			SELECT 
			@UserName = [User].UserName,
			@UserForenames = [User].Forenames,
			@UserSurname = [User].Surname
		FROM Staff 
			INNER JOIN [User] ON [User].UserID = Staff.UserID
		WHERE 
			StaffID = @StaffId;
	END


	-- ***********************************************************
	--  Create the InYearGrade record if it doesnt exist
	-- ***********************************************************
	SELECT 
		@InYearGradeID = InYearGrade.ID 
	FROM 
		InYearGrade 
	WHERE 
		AcademicYearID = @AcademicYearID    AND 
		CourseID       = @OriginalCourseID  AND
		StudentID      = @StudentID;

	IF @InYearGradeID IS NULL
	BEGIN
		Print '@InYearGradeID IS NULL';

		INSERT INTO InYearGrade 
			(AcademicYearID, StudentID, CourseID, 
			 Point1_Grade, Point2_Grade, Point3_Grade, Point4_Grade, Point5_Grade, Point6_Grade, Point7_Grade, Point8_Grade, Point9_Grade, Point10_Grade, Point11_Grade, Point12_Grade,
			 Point1_Grade_Updated, Point2_Grade_Updated, Point3_Grade_Updated, Point4_Grade_Updated, Point5_Grade_Updated, Point6_Grade_Updated, Point7_Grade_Updated, Point8_Grade_Updated, Point9_Grade_Updated, Point10_Grade_Updated, Point11_Grade_Updated, Point12_Grade_Updated, 
			 PersonalTargetGrade,
			 Point1_Notes, Point2_Notes, Point3_Notes, Point4_Notes, Point5_Notes, Point6_Notes, Point7_Notes, Point8_Notes, Point9_Notes, Point10_Notes, Point11_Notes, Point12_Notes, 
			 LearningAimRef,
			 Point1_EffortID, Point2_EffortID, Point3_EffortID, Point4_EffortID, Point5_EffortID, Point6_EffortID, Point7_EffortID, Point8_EffortID, Point9_EffortID, Point10_EffortID, Point11_EffortID, Point12_EffortID 
			 ) 
		VALUES 
			(
				--Note: we write the Orig Course ID
				@AcademicYearID, @StudentID, @OriginalCourseID,
				NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
				0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
				Null,
				NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
				null,
				NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
			);
		
		--get the new ID
		SELECT @InYearGradeID = SCOPE_IDENTITY();
	END

	Print '@InYearGradeID=' + CONVERT(varchar(10), @InYearGradeID);
	

	-- ***********************************************************
	--  Update the InYearGrade table
	-- ***********************************************************
	UPDATE 
		InYearGrade
	SET 
		PersonalTargetGrade = (IIF(@PersonalTargetGrade = '', Null, @PersonalTargetGrade)),
		LearningAimRef = @LearningAimRef,

		Point1_Grade = (IIF(@Point1_Grade = '', Null, @Point1_Grade)),
		Point2_Grade = (IIF(@Point2_Grade = '', Null, @Point2_Grade)),
		Point3_Grade = (IIF(@Point3_Grade = '', Null, @Point3_Grade)),
		Point4_Grade = (IIF(@Point4_Grade = '', Null, @Point4_Grade)),
		Point5_Grade = (IIF(@Point5_Grade = '', Null, @Point5_Grade)),
		Point6_Grade = (IIF(@Point6_Grade = '', Null, @Point6_Grade)),
		Point7_Grade = (IIF(@Point7_Grade = '', Null, @Point7_Grade)),
		Point8_Grade = (IIF(@Point8_Grade = '', Null, @Point8_Grade)),
		Point9_Grade = (IIF(@Point9_Grade = '', Null, @Point9_Grade)),
		Point10_Grade = (IIF(@Point10_Grade = '', Null, @Point10_Grade)),
		Point11_Grade = (IIF(@Point11_Grade = '', Null, @Point11_Grade)),
		Point12_Grade = (IIF(@Point12_Grade = '', Null, @Point12_Grade)),

		Point1_Grade_Updated = (CASE WHEN @Point1_Grade_Existing = @Point1_Grade THEN 0 ELSE 1 END),
		Point2_Grade_Updated = (CASE WHEN @Point2_Grade_Existing = @Point2_Grade THEN 0 ELSE 1 END),
		Point3_Grade_Updated = (CASE WHEN @Point3_Grade_Existing = @Point3_Grade THEN 0 ELSE 1 END),
		Point4_Grade_Updated = (CASE WHEN @Point4_Grade_Existing = @Point4_Grade THEN 0 ELSE 1 END),
		Point5_Grade_Updated = (CASE WHEN @Point5_Grade_Existing = @Point5_Grade THEN 0 ELSE 1 END),
		Point6_Grade_Updated = (CASE WHEN @Point6_Grade_Existing = @Point6_Grade THEN 0 ELSE 1 END),
		Point7_Grade_Updated = (CASE WHEN @Point7_Grade_Existing = @Point7_Grade THEN 0 ELSE 1 END),
		Point8_Grade_Updated = (CASE WHEN @Point8_Grade_Existing = @Point8_Grade THEN 0 ELSE 1 END),
		Point9_Grade_Updated = (CASE WHEN @Point9_Grade_Existing = @Point9_Grade THEN 0 ELSE 1 END),
		Point10_Grade_Updated = (CASE WHEN @Point10_Grade_Existing = @Point10_Grade THEN 0 ELSE 1 END),
		Point11_Grade_Updated = (CASE WHEN @Point11_Grade_Existing = @Point11_Grade THEN 0 ELSE 1 END),
		Point12_Grade_Updated = (CASE WHEN @Point12_Grade_Existing = @Point12_Grade THEN 0 ELSE 1 END),

		Point1_Notes = (IIF(@Point1_Notes = '', Null, @Point1_Notes)),
		Point2_Notes = (IIF(@Point2_Notes = '', Null, @Point2_Notes)),
		Point3_Notes = (IIF(@Point3_Notes = '', Null, @Point3_Notes)),
		Point4_Notes = (IIF(@Point4_Notes = '', Null, @Point4_Notes)),
		Point5_Notes = (IIF(@Point5_Notes = '', Null, @Point5_Notes)),
		Point6_Notes = (IIF(@Point6_Notes = '', Null, @Point6_Notes)),
		Point7_Notes = (IIF(@Point7_Notes = '', Null, @Point7_Notes)),
		Point8_Notes = (IIF(@Point8_Notes = '', Null, @Point8_Notes)),
		Point9_Notes = (IIF(@Point9_Notes = '', Null, @Point9_Notes)),
		Point10_Notes = (IIF(@Point10_Notes = '', Null, @Point10_Notes)),
		Point11_Notes = (IIF(@Point11_Notes = '', Null, @Point11_Notes)),
		Point12_Notes = (IIF(@Point12_Notes = '', Null, @Point12_Notes)),

		Point1_EffortID = (IIF(@Point1_EffortID <= 0, Null, @Point1_EffortID)),
		Point2_EffortID = (IIF(@Point2_EffortID <= 0, Null, @Point2_EffortID)),
		Point3_EffortID = (IIF(@Point3_EffortID <= 0, Null, @Point3_EffortID)),
		Point4_EffortID = (IIF(@Point4_EffortID <= 0, Null, @Point4_EffortID)),
		Point5_EffortID = (IIF(@Point5_EffortID <= 0, Null, @Point5_EffortID)),
		Point6_EffortID = (IIF(@Point6_EffortID <= 0, Null, @Point6_EffortID)),
		Point7_EffortID = (IIF(@Point7_EffortID <= 0, Null, @Point7_EffortID)),
		Point8_EffortID = (IIF(@Point8_EffortID <= 0, Null, @Point8_EffortID)),
		Point9_EffortID = (IIF(@Point9_EffortID <= 0, Null, @Point9_EffortID)),
		Point10_EffortID = (IIF(@Point10_EffortID <= 0, Null, @Point10_EffortID)),
		Point11_EffortID = (IIF(@Point11_EffortID <= 0, Null, @Point11_EffortID)),
		Point12_EffortID = (IIF(@Point12_EffortID <= 0, Null, @Point12_EffortID))
	WHERE
		ID = @InYearGradeID;


	-- ***********************************************************
	--  Populate EnrolmentWebSave
	-- ***********************************************************
	DELETE FROM EnrolmentWebSave WHERE EnrolmentID = @EnrolmentID AND UserName = @UserName;
	INSERT INTO EnrolmentWebSave (EnrolmentID, UserName) VALUES (@EnrolmentID, @UserName);


	-- ***********************************************************
	--  Regenerate the EstactVA data 
	--  We now do this separately to speed up the whole process i.e. when multiple Student changes
	-- ***********************************************************
	--EXEC sp_Student_EstActVA_WebSave_Regenerate @AcademicYearID, @UserName


	-- ***********************************************************
	--  Construct the RowDesc e.g. 22/23,Firstname_450882 450882,21271W
	-- ***********************************************************
	SET @RowDescription = (@AcademicYearID + ',' + @Forenames + ' ' + @Surname + ',' + @CourseCode);


	-- ***********************************************************
	--  Create Audit entries for the changed Grade fields
	-- ***********************************************************
	IF @Point1_Grade != @Point1_Grade_Existing BEGIN
	    IF IsNull(@Point1_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point1_Grade', null, null, @Point1_Grade_Existing, @Point1_Grade)
	END
	IF @Point2_Grade != @Point2_Grade_Existing BEGIN
	    IF IsNull(@Point2_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point2_Grade', null, null, @Point2_Grade_Existing, @Point2_Grade)
	END
	IF @Point3_Grade != @Point3_Grade_Existing BEGIN
	    IF IsNull(@Point3_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point3_Grade', null, null, @Point3_Grade_Existing, @Point3_Grade)
	END
	IF @Point4_Grade != @Point4_Grade_Existing BEGIN
	    IF IsNull(@Point4_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point4_Grade', null, null, @Point4_Grade_Existing, @Point4_Grade)
	END
	IF @Point5_Grade != @Point5_Grade_Existing BEGIN
	    IF IsNull(@Point5_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point5_Grade', null, null, @Point5_Grade_Existing, @Point5_Grade)
	END
	IF @Point6_Grade != @Point6_Grade_Existing BEGIN
	    IF IsNull(@Point6_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point6_Grade', null, null, @Point6_Grade_Existing, @Point6_Grade)
	END
	IF @Point7_Grade != @Point7_Grade_Existing BEGIN
	    IF IsNull(@Point7_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point7_Grade', null, null, @Point7_Grade_Existing, @Point7_Grade)
	END
	IF @Point8_Grade != @Point8_Grade_Existing BEGIN
	    IF IsNull(@Point8_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point8_Grade', null, null, @Point8_Grade_Existing, @Point8_Grade)
	END
	IF @Point9_Grade != @Point9_Grade_Existing BEGIN
	    IF IsNull(@Point9_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point9_Grade', null, null, @Point9_Grade_Existing, @Point9_Grade)
	END
	IF @Point10_Grade != @Point10_Grade_Existing BEGIN
	    IF IsNull(@Point10_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point10_Grade', null, null, @Point10_Grade_Existing, @Point10_Grade)
	END
	IF @Point11_Grade != @Point11_Grade_Existing BEGIN
	    IF IsNull(@Point11_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point11_Grade', null, null, @Point11_Grade_Existing, @Point11_Grade)
	END
	IF @Point12_Grade != @Point12_Grade_Existing BEGIN
	    IF IsNull(@Point12_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point12_Grade', null, null, @Point12_Grade_Existing, @Point12_Grade)
	END


	-- ***********************************************************
	--   Create Audit entries for the changed Notes fields
	-- ***********************************************************
	IF @Point1_Notes != @Point1_Notes_Existing BEGIN
	    IF IsNull(@Point1_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point1_Notes', null, null, @Point1_Notes_Existing, @Point1_Notes)
	END
	IF @Point2_Notes != @Point2_Notes_Existing BEGIN
	    IF IsNull(@Point2_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point2_Notes', null, null, @Point2_Notes_Existing, @Point2_Notes)
	END
	IF @Point3_Notes != @Point3_Notes_Existing BEGIN
	    IF IsNull(@Point3_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point3_Notes', null, null, @Point3_Notes_Existing, @Point3_Notes)
	END
	IF @Point4_Notes != @Point4_Notes_Existing BEGIN
	    IF IsNull(@Point4_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point4_Notes', null, null, @Point4_Notes_Existing, @Point4_Notes)
	END
	IF @Point5_Notes != @Point5_Notes_Existing BEGIN
	    IF IsNull(@Point5_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point5_Notes', null, null, @Point5_Notes_Existing, @Point5_Notes)
	END
	IF @Point6_Notes != @Point6_Notes_Existing BEGIN
	    IF IsNull(@Point6_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point6_Notes', null, null, @Point6_Notes_Existing, @Point6_Notes)
	END
	IF @Point7_Notes != @Point7_Notes_Existing BEGIN
	    IF IsNull(@Point7_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point7_Notes', null, null, @Point7_Notes_Existing, @Point7_Notes)
	END
	IF @Point8_Notes != @Point8_Notes_Existing BEGIN
	    IF IsNull(@Point8_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point8_Notes', null, null, @Point8_Notes_Existing, @Point8_Notes)
	END
	IF @Point9_Notes != @Point9_Notes_Existing BEGIN
	    IF IsNull(@Point9_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point9_Notes', null, null, @Point9_Notes_Existing, @Point9_Notes)
	END
	IF @Point10_Notes != @Point10_Notes_Existing BEGIN
	    IF IsNull(@Point10_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point10_Notes', null, null, @Point10_Notes_Existing, @Point10_Notes)
	END
	IF @Point11_Notes != @Point11_Notes_Existing BEGIN
	    IF IsNull(@Point11_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point11_Notes', null, null, @Point11_Notes_Existing, @Point11_Notes)
	END
	IF @Point12_Notes != @Point12_Notes_Existing BEGIN
	    IF IsNull(@Point12_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point12_Notes', null, null, @Point12_Notes_Existing, @Point12_Notes)
	END


	-- ***********************************************************
	--  Create Audit entries for the changed EffortGrade fields
	-- ***********************************************************
	IF @Point1_EffortDescription_New != @Point1_EffortDescription_Existing BEGIN
	    IF IsNull(@Point1_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point1_EffortID', null, null, @Point1_EffortDescription_Existing, @Point1_EffortDescription_New)
	END
	--Print '2 New      InYrDesc=' + @Point2_EffortDescription_New;
	--Print '2 Existing InYrDesc=' + @Point2_EffortDescription_Existing;
	IF @Point2_EffortDescription_New != @Point2_EffortDescription_Existing BEGIN
	    IF IsNull(@Point2_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point2_EffortID', null, null, @Point2_EffortDescription_Existing, @Point2_EffortDescription_New)
	END
	IF @Point3_EffortDescription_New != @Point3_EffortDescription_Existing BEGIN
	    IF IsNull(@Point3_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point3_EffortID', null, null, @Point3_EffortDescription_Existing, @Point3_EffortDescription_New)
	END
	IF @Point4_EffortDescription_New != @Point4_EffortDescription_Existing BEGIN
	    IF IsNull(@Point4_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point4_EffortID', null, null, @Point4_EffortDescription_Existing, @Point4_EffortDescription_New)
	END
	IF @Point5_EffortDescription_New != @Point5_EffortDescription_Existing BEGIN
	    IF IsNull(@Point5_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point5_EffortID', null, null, @Point5_EffortDescription_Existing, @Point5_EffortDescription_New)
	END
	IF @Point6_EffortDescription_New != @Point6_EffortDescription_Existing BEGIN
	    IF IsNull(@Point6_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point6_EffortID', null, null, @Point6_EffortDescription_Existing, @Point6_EffortDescription_New)
	END
	IF @Point7_EffortDescription_New != @Point7_EffortDescription_Existing BEGIN
	    IF IsNull(@Point7_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point7_EffortID', null, null, @Point7_EffortDescription_Existing, @Point7_EffortDescription_New)
	END
	IF @Point8_EffortDescription_New != @Point8_EffortDescription_Existing BEGIN
	    IF IsNull(@Point8_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point8_EffortID', null, null, @Point8_EffortDescription_Existing, @Point8_EffortDescription_New)
	END
	IF @Point9_EffortDescription_New != @Point9_EffortDescription_Existing BEGIN
	    IF IsNull(@Point9_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point9_EffortID', null, null, @Point9_EffortDescription_Existing, @Point9_EffortDescription_New)
	END
	IF @Point10_EffortDescription_New != @Point10_EffortDescription_Existing BEGIN
	    IF IsNull(@Point10_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point10_EffortID', null, null, @Point10_EffortDescription_Existing, @Point10_EffortDescription_New)
	END
	IF @Point11_EffortDescription_New != @Point11_EffortDescription_Existing BEGIN
	    IF IsNull(@Point11_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point11_EffortID', null, null, @Point11_EffortDescription_Existing, @Point11_EffortDescription_New)
	END
	IF @Point12_EffortDescription_New != @Point12_EffortDescription_Existing BEGIN
	    IF IsNull(@Point12_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'Point12_EffortID', null, null, @Point12_EffortDescription_Existing, @Point12_EffortDescription_New)
	END


	-- ***********************************************************
	--  Create Audit entries for the changed PersonalTargetGrade fields
	-- ***********************************************************
	IF @PersonalTargetGrade != @PersonalTargetGrade_Existing BEGIN
	    IF IsNull(@PersonalTargetGrade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'InYearGrade', @InYearGradeID, @RowDescription, 'PersonalTargetGrade', null, null, @PersonalTargetGrade_Existing, @PersonalTargetGrade)
	END	


	-- ***********************************************************
	--   Return 1 for success
	-- ***********************************************************
	SELECT 1;

END
ELSE
BEGIN
    Print 'EstactVA ID Doesnt Exist';

    --ID doesnt exist so return -1
	SELECT -1;
END




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







ALTER PROCEDURE [dbo].[sp_WebPlus_EMStudent_Update]
@StaffId              INTEGER,
@EngMatType           VARCHAR(8),
@ID                   INTEGER,
@PersonalTargetGrade  VARCHAR(255),
@IsStartGradeChanged  BIT,
@StartGrade           VARCHAR(20)  = null,
@StartPoints          DECIMAL(9,2) = null,
@Point1_Grade   VARCHAR(20),
@Point2_Grade   VARCHAR(20), 
@Point3_Grade   VARCHAR(20), 
@Point4_Grade   VARCHAR(20), 
@Point5_Grade   VARCHAR(20), 
@Point6_Grade   VARCHAR(20), 
@Point7_Grade   VARCHAR(20), 
@Point8_Grade   VARCHAR(20), 
@Point9_Grade   VARCHAR(20), 
@Point10_Grade  VARCHAR(20), 
@Point11_Grade  VARCHAR(20), 
@Point12_Grade  VARCHAR(20), 
@Point1_Notes   VARCHAR(1024),
@Point2_Notes   VARCHAR(1024),
@Point3_Notes   VARCHAR(1024),
@Point4_Notes   VARCHAR(1024),
@Point5_Notes   VARCHAR(1024),
@Point6_Notes   VARCHAR(1024),
@Point7_Notes   VARCHAR(1024),
@Point8_Notes   VARCHAR(1024),
@Point9_Notes   VARCHAR(1024),
@Point10_Notes  VARCHAR(1024),
@Point11_Notes  VARCHAR(1024),
@Point12_Notes  VARCHAR(1024),
@Point1_EffortID INT,
@Point2_EffortID INT,
@Point3_EffortID INT,
@Point4_EffortID INT,
@Point5_EffortID INT,
@Point6_EffortID INT,
@Point7_EffortID INT,
@Point8_EffortID INT,
@Point9_EffortID INT,
@Point10_EffortID INT,
@Point11_EffortID INT,
@Point12_EffortID INT

AS
-- =============================================
-- Author:		Andrew Breward
-- Created:     AndyB 2/2/2023 
-- Modified:	AndyB 23/09/2023 @PersonalTargetGrade_Existing is now VARCHAR(255)
--              AndyB 11/10/2023 Create AuditTrail entries for changed values
--              AndyB 15/01/2024 Changed AuditChange value to either INSERT or UPDATE
--              AndyB 30/01/2024 Fixed bug whereby StartGrade and points not written correctly (with or without IYGrade also being changed)
--              AndyB 10/04/2024 v1.37.2 Bugfix: write UserName instead of StaffCode (join on UserID)
--				ChrisC 06/03/26 v1.41 Bug fix looks at OAuth when in OAuth mode for username
--
-- Descripton:	SP to Update the EngMat_Enrolment_InYear (Grades and Notes) values
-- Example:     exec sp_WebPlus_EMStudent_Update 123, 'English', 123456, ...
-- =============================================

DECLARE @newID          INT;
DECLARE @InYearGradeID  INT;
DECLARE @AcademicYearID VARCHAR(5);
DECLARE @LearningAimRef VARCHAR(8);
DECLARE @StudentID      INT;
DECLARE @EnrolmentID    INT;
DECLARE @CourseID       INT;
DECLARE @OriginalCourseID INT;

DECLARE @UserName       VARCHAR(255);
DECLARE @UserForenames  VARCHAR(100);
DECLARE @UserSurname    VARCHAR(100);

DECLARE @RowDescription VARCHAR(500);

DECLARE @Forenames      VARCHAR(100);
DECLARE @Surname        VARCHAR(100);
DECLARE @CourseCode     VARCHAR(50);

DECLARE @StartGradeUserName   VARCHAR(100);
DECLARE @StartGradeDateTime   DATETIME;
DECLARE @StartGradeGrade      VARCHAR(20);
DECLARE @StartGradePoints     DECIMAL(9,2);

DECLARE @Point1_Grade_Existing VARCHAR(20);
DECLARE @Point2_Grade_Existing VARCHAR(20);
DECLARE @Point3_Grade_Existing VARCHAR(20);
DECLARE @Point4_Grade_Existing VARCHAR(20);
DECLARE @Point5_Grade_Existing VARCHAR(20);
DECLARE @Point6_Grade_Existing VARCHAR(20);
DECLARE @Point7_Grade_Existing VARCHAR(20);
DECLARE @Point8_Grade_Existing VARCHAR(20);
DECLARE @Point9_Grade_Existing VARCHAR(20);
DECLARE @Point10_Grade_Existing VARCHAR(20);
DECLARE @Point11_Grade_Existing VARCHAR(20);
DECLARE @Point12_Grade_Existing VARCHAR(20);

DECLARE @Point1_Notes_Existing VARCHAR(1024);
DECLARE @Point2_Notes_Existing VARCHAR(1024);
DECLARE @Point3_Notes_Existing VARCHAR(1024);
DECLARE @Point4_Notes_Existing VARCHAR(1024);
DECLARE @Point5_Notes_Existing VARCHAR(1024);
DECLARE @Point6_Notes_Existing VARCHAR(1024);
DECLARE @Point7_Notes_Existing VARCHAR(1024);
DECLARE @Point8_Notes_Existing VARCHAR(1024);
DECLARE @Point9_Notes_Existing VARCHAR(1024);
DECLARE @Point10_Notes_Existing VARCHAR(1024);
DECLARE @Point11_Notes_Existing VARCHAR(1024);
DECLARE @Point12_Notes_Existing VARCHAR(1024);

DECLARE @Point1_EffortDescription_Existing VARCHAR(100);
DECLARE @Point2_EffortDescription_Existing VARCHAR(100);
DECLARE @Point3_EffortDescription_Existing VARCHAR(100);
DECLARE @Point4_EffortDescription_Existing VARCHAR(100);
DECLARE @Point5_EffortDescription_Existing VARCHAR(100);
DECLARE @Point6_EffortDescription_Existing VARCHAR(100);
DECLARE @Point7_EffortDescription_Existing VARCHAR(100);
DECLARE @Point8_EffortDescription_Existing VARCHAR(100);
DECLARE @Point9_EffortDescription_Existing VARCHAR(100);
DECLARE @Point10_EffortDescription_Existing VARCHAR(100);
DECLARE @Point11_EffortDescription_Existing VARCHAR(100);
DECLARE @Point12_EffortDescription_Existing VARCHAR(100);

DECLARE @Point1_EffortDescription_New VARCHAR(100);
DECLARE @Point2_EffortDescription_New VARCHAR(100);
DECLARE @Point3_EffortDescription_New VARCHAR(100);
DECLARE @Point4_EffortDescription_New VARCHAR(100);
DECLARE @Point5_EffortDescription_New VARCHAR(100);
DECLARE @Point6_EffortDescription_New VARCHAR(100);
DECLARE @Point7_EffortDescription_New VARCHAR(100);
DECLARE @Point8_EffortDescription_New VARCHAR(100);
DECLARE @Point9_EffortDescription_New VARCHAR(100);
DECLARE @Point10_EffortDescription_New VARCHAR(100);
DECLARE @Point11_EffortDescription_New VARCHAR(100);
DECLARE @Point12_EffortDescription_New VARCHAR(100);

DECLARE @Point1_EffortID_Existing INT;
DECLARE @Point2_EffortID_Existing INT;
DECLARE @Point3_EffortID_Existing INT;
DECLARE @Point4_EffortID_Existing INT;
DECLARE @Point5_EffortID_Existing INT;
DECLARE @Point6_EffortID_Existing INT;
DECLARE @Point7_EffortID_Existing INT;
DECLARE @Point8_EffortID_Existing INT;
DECLARE @Point9_EffortID_Existing INT;
DECLARE @Point10_EffortID_Existing INT;
DECLARE @Point11_EffortID_Existing INT;
DECLARE @Point12_EffortID_Existing INT;

DECLARE @PersonalTargetGrade_Existing VARCHAR(255);

DECLARE @TypeOfChange VARCHAR(50);

-- ***********************************************************
--  Check if the Student ID already exists
-- ***********************************************************
IF EXISTS (SELECT 1 FROM EngMat_Enrolment_InYear WHERE EngMat_Enrolment_InYear.ID = @ID)
BEGIN

	-- ***********************************************************
	--  Set the default StartGrade values
	-- ***********************************************************
	SET @StartGradeUserName = NULL;
	SET @StartGradeDateTime = NULL;
	SET @StartGradeGrade    = NULL; -- @StartGrade;    
	SET @StartGradePoints   = NULL;

	-- ***********************************************************
	--  Get the details from the main table: EngMat_Enrolment_InYear
	-- ***********************************************************
	SELECT 
		--@EnrolmentID = EngMatEnrolment.ID,
		@AcademicYearID = EngMat_Enrolment_InYear.AcademicYearID,
		@Forenames = ISNULL(EngMat_Enrolment_InYear.Forenames, ''),
		@Surname = ISNULL(EngMat_Enrolment_InYear.Surname, ''),
		@CourseCode = ISNULL(EngMat_Enrolment_InYear.CourseCode, ''),
		@StudentID = EngMat_Enrolment_InYear.EngMatStudentID,
		@EnrolmentID = EngMat_Enrolment_InYear.ID,
		@LearningAimRef = EngMat_Enrolment_InYear.LearningAimRef,
		@CourseID = EngMat_Enrolment_InYear.CourseID,
		@OriginalCourseID = OGP_Enrolment.OriginalCourseID,
		@PersonalTargetGrade_Existing = ISNULL(EngMat_Enrolment_InYear.PersonalTargetGrade, ''),
		--
		@StartGradeGrade  = OGHighestPreviousGCSEGrade,
		@StartGradePoints = OGHighestPreviousGCSEPoints,
		@StartGradeUserName = OGHighestPreviousGCSEGradeSavedBy, 
		@StartGradeDateTime = OGHighestPreviousGCSEGradeSavedWhen,
		--
		@Point1_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade1, ''),
		@Point2_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade2, ''),
		@Point3_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade3, ''),
		@Point4_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade4, ''),
		@Point5_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade5, ''),
		@Point6_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade6, ''),
		@Point7_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade7, ''),
		@Point8_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade8, ''),
		@Point9_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade9, ''),
		@Point10_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade10, ''),
		@Point11_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade11, ''),
		@Point12_Grade_Existing = ISNULL(EngMat_Enrolment_InYear.IYGrade12, ''),
		--
		@Point1_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point1_Notes, ''),
		@Point2_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point2_Notes, ''),
		@Point3_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point3_Notes, ''),
		@Point4_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point4_Notes, ''),
		@Point5_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point5_Notes, ''),
		@Point6_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point6_Notes, ''),
		@Point7_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point7_Notes, ''),
		@Point8_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point8_Notes, ''),
		@Point9_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point9_Notes, ''),
		@Point10_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point10_Notes, ''),
		@Point11_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point11_Notes, ''),
		@Point12_Notes_Existing = ISNULL(EngMat_Enrolment_InYear.Point12_Notes, ''),
--
		@Point1_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point1_EffortDescription, ''),
		@Point2_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point2_EffortDescription, ''),
		@Point3_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point3_EffortDescription, ''),
		@Point4_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point4_EffortDescription, ''),
		@Point5_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point5_EffortDescription, ''),
		@Point6_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point6_EffortDescription, ''),
		@Point7_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point7_EffortDescription, ''),
		@Point8_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point8_EffortDescription, ''),
		@Point9_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point9_EffortDescription, ''),
		@Point10_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point10_EffortDescription, ''),
		@Point11_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point11_EffortDescription, ''),
		@Point12_EffortDescription_Existing = ISNULL(EngMat_Enrolment_InYear.Point12_EffortDescription, '')
	 FROM 
		EngMat_Enrolment_InYear 
				INNER JOIN OGP_Enrolment  ON OGP_Enrolment.StudentRef = EngMat_Enrolment_InYear.StudentRef AND 
				                             OGP_Enrolment.AcademicYearID = EngMat_Enrolment_InYear.AcademicYearID AND
											 OGP_Enrolment.CourseID = EngMat_Enrolment_InYear.CourseID

		--	INNER JOIN EngMatEnrolment ON
		--		EngMatEnrolment.EngMatStudentID = EngMat_Enrolment_InYear.EngMatStudentID AND 
		--		EngMatEnrolment.CourseID = EngMat_Enrolment_InYear.CourseID
	 WHERE 
		EngMat_Enrolment_InYear.ID = @ID; 


	-- ***********************************************************
	--  Get the EffortGrade Description from the ID
	-- ***********************************************************
	SELECT @Point1_EffortDescription_New = '';
	SELECT @Point2_EffortDescription_New = '';
	SELECT @Point3_EffortDescription_New = '';
	SELECT @Point4_EffortDescription_New = '';
	SELECT @Point5_EffortDescription_New = '';
	SELECT @Point6_EffortDescription_New = '';
	SELECT @Point7_EffortDescription_New = '';
	SELECT @Point8_EffortDescription_New = '';
	SELECT @Point9_EffortDescription_New = '';
	SELECT @Point10_EffortDescription_New = '';
	SELECT @Point11_EffortDescription_New = '';
	SELECT @Point12_EffortDescription_New = '';

	SELECT @Point1_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point1_EffortID, 0);
	SELECT @Point2_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point2_EffortID, 0);
	SELECT @Point3_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point3_EffortID, 0);
	SELECT @Point4_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point4_EffortID, 0);
	SELECT @Point5_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point5_EffortID, 0);
	SELECT @Point6_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point6_EffortID, 0);
	SELECT @Point7_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point7_EffortID, 0);
	SELECT @Point8_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point8_EffortID, 0);
	SELECT @Point9_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point9_EffortID, 0);
	SELECT @Point10_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point10_EffortID, 0);
	SELECT @Point11_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point11_EffortID, 0);
	SELECT @Point12_EffortDescription_New = ISNULL(DESCRIPTION, '') FROM InYearGradeEffort WHERE ID = ISNULL(@Point12_EffortID, 0);


	-- ***********************************************************
	--  Get the Staff Username
	-- SELECT @UserName = StaffCode FROM Staff WHERE StaffID = @StaffId;
	-- ***********************************************************
	--SELECT 
	--	@UserName = [User].UserName,
	--	@UserForenames = [User].Forenames,
	--	@UserSurname = [User].Surname
	--FROM Staff 
	--	INNER JOIN [User] ON [User].UserID = Staff.UserID
	--WHERE 
	--    StaffID = @StaffId;

		
	DECLARE @SysValueValue VARCHAR(255)
    SELECT @SysValueValue = SystemSettingValue.SystemSettingValue FROM SystemSettingValue WHERE SystemSettingValue.SystemSettingName = 'OneGradePlusAuthenticationType'
	
	IF @SysValueValue = 'OAUTH'
	BEGIN
		SELECT 
			@UserName = [User].OAuthUserName,
			@UserForenames = [User].Forenames,
			@UserSurname = [User].Surname
		FROM Staff 
			INNER JOIN [User] ON [User].UserID = Staff.UserID
		WHERE 
			StaffID = @StaffId
	END
	ELSE
	BEGIN
			SELECT 
			@UserName = [User].UserName,
			@UserForenames = [User].Forenames,
			@UserSurname = [User].Surname
		FROM Staff 
			INNER JOIN [User] ON [User].UserID = Staff.UserID
		WHERE 
			StaffID = @StaffId;
	END


	-- ***********************************************************
	--  Always update the Start Grade (if set to something)
	-- ***********************************************************
	IF @StartGrade IS NOT NULL
	BEGIN
		SET @StartGradeGrade = @StartGrade;
	END


	-- ***********************************************************
	--  Set the Other StartGrade values based on the parameters
	-- ***********************************************************
	IF @IsStartGradeChanged = 1
	BEGIN
		SET @StartGradeUserName = (@UserForenames + ' ' + @UserSurname);
		SET @StartGradeDateTime = GETDATE();
		SET @StartGradePoints = @StartPoints;
	END


	-- ***********************************************************
	--  Create the EngMatInYearGrade record if it doesnt exist
	-- ***********************************************************
	SELECT 
		@InYearGradeID = EngMatInYearGrade.ID 
	FROM 
		EngMatInYearGrade 
	WHERE 
		AcademicYearID  = @AcademicYearID    AND 
		CourseID        = @OriginalCourseID  AND
		EngMatStudentID = @StudentID;

	IF @InYearGradeID IS NULL
	BEGIN
		INSERT INTO EngMatInYearGrade 
			(AcademicYearID, EngMatStudentID, CourseID, 
			 Point1_Grade, Point2_Grade, Point3_Grade, Point4_Grade, Point5_Grade, Point6_Grade, Point7_Grade, Point8_Grade, Point9_Grade, Point10_Grade, Point11_Grade, Point12_Grade,
			 Point1_Grade_Updated, Point2_Grade_Updated, Point3_Grade_Updated, Point4_Grade_Updated, Point5_Grade_Updated, Point6_Grade_Updated, Point7_Grade_Updated, Point8_Grade_Updated, Point9_Grade_Updated, Point10_Grade_Updated, Point11_Grade_Updated, Point12_Grade_Updated, 
			 PersonalTargetGrade,
			 Point1_Notes, Point2_Notes, Point3_Notes, Point4_Notes, Point5_Notes, Point6_Notes, Point7_Notes, Point8_Notes, Point9_Notes, Point10_Notes, Point11_Notes, Point12_Notes, 
			 LearningAimRef,
			 Point1_EffortID, Point2_EffortID, Point3_EffortID, Point4_EffortID, Point5_EffortID, Point6_EffortID, Point7_EffortID, Point8_EffortID, Point9_EffortID, Point10_EffortID, Point11_EffortID, Point12_EffortID,
			 OGHighestPreviousGCSEGrade, OGHighestPreviousGCSEPoints, OGHighestPreviousGCSEGradeSavedBy, OGHighestPreviousGCSEGradeSavedWhen 
			 ) 
		VALUES 
			(
				--Note: we write the Orig Course ID
				@AcademicYearID, @StudentID, @OriginalCourseID,
				NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
				0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
				@PersonalTargetGrade,
				NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
				@LearningAimRef,
				NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
				NULL, NULL, NULL, NULL
			);
		
		--get the new ID
		SELECT @InYearGradeID = SCOPE_IDENTITY();
	END

	-- ***********************************************************
	--  Update the EngMatInYearGrade table
	-- ***********************************************************
	UPDATE 
		EngMatInYearGrade
	SET 
		PersonalTargetGrade = (IIF(@PersonalTargetGrade = '', Null, @PersonalTargetGrade)),
		LearningAimRef = @LearningAimRef,

		Point1_Grade = (IIF(@Point1_Grade = '', Null, @Point1_Grade)),
		Point2_Grade = (IIF(@Point2_Grade = '', Null, @Point2_Grade)),
		Point3_Grade = (IIF(@Point3_Grade = '', Null, @Point3_Grade)),
		Point4_Grade = (IIF(@Point4_Grade = '', Null, @Point4_Grade)),
		Point5_Grade = (IIF(@Point5_Grade = '', Null, @Point5_Grade)),
		Point6_Grade = (IIF(@Point6_Grade = '', Null, @Point6_Grade)),
		Point7_Grade = (IIF(@Point7_Grade = '', Null, @Point7_Grade)),
		Point8_Grade = (IIF(@Point8_Grade = '', Null, @Point8_Grade)),
		Point9_Grade = (IIF(@Point9_Grade = '', Null, @Point9_Grade)),
		Point10_Grade = (IIF(@Point10_Grade = '', Null, @Point10_Grade)),
		Point11_Grade = (IIF(@Point11_Grade = '', Null, @Point11_Grade)),
		Point12_Grade = (IIF(@Point12_Grade = '', Null, @Point12_Grade)),

		Point1_Grade_Updated = (CASE WHEN @Point1_Grade_Existing = @Point1_Grade THEN 0 ELSE 1 END),
		Point2_Grade_Updated = (CASE WHEN @Point2_Grade_Existing = @Point2_Grade THEN 0 ELSE 1 END),
		Point3_Grade_Updated = (CASE WHEN @Point3_Grade_Existing = @Point3_Grade THEN 0 ELSE 1 END),
		Point4_Grade_Updated = (CASE WHEN @Point4_Grade_Existing = @Point4_Grade THEN 0 ELSE 1 END),
		Point5_Grade_Updated = (CASE WHEN @Point5_Grade_Existing = @Point5_Grade THEN 0 ELSE 1 END),
		Point6_Grade_Updated = (CASE WHEN @Point6_Grade_Existing = @Point6_Grade THEN 0 ELSE 1 END),
		Point7_Grade_Updated = (CASE WHEN @Point7_Grade_Existing = @Point7_Grade THEN 0 ELSE 1 END),
		Point8_Grade_Updated = (CASE WHEN @Point8_Grade_Existing = @Point8_Grade THEN 0 ELSE 1 END),
		Point9_Grade_Updated = (CASE WHEN @Point9_Grade_Existing = @Point9_Grade THEN 0 ELSE 1 END),
		Point10_Grade_Updated = (CASE WHEN @Point10_Grade_Existing = @Point10_Grade THEN 0 ELSE 1 END),
		Point11_Grade_Updated = (CASE WHEN @Point11_Grade_Existing = @Point11_Grade THEN 0 ELSE 1 END),
		Point12_Grade_Updated = (CASE WHEN @Point12_Grade_Existing = @Point12_Grade THEN 0 ELSE 1 END),

		Point1_Notes = (IIF(@Point1_Notes = '', Null, @Point1_Notes)),
		Point2_Notes = (IIF(@Point2_Notes = '', Null, @Point2_Notes)),
		Point3_Notes = (IIF(@Point3_Notes = '', Null, @Point3_Notes)),
		Point4_Notes = (IIF(@Point4_Notes = '', Null, @Point4_Notes)),
		Point5_Notes = (IIF(@Point5_Notes = '', Null, @Point5_Notes)),
		Point6_Notes = (IIF(@Point6_Notes = '', Null, @Point6_Notes)),
		Point7_Notes = (IIF(@Point7_Notes = '', Null, @Point7_Notes)),
		Point8_Notes = (IIF(@Point8_Notes = '', Null, @Point8_Notes)),
		Point9_Notes = (IIF(@Point9_Notes = '', Null, @Point9_Notes)),
		Point10_Notes = (IIF(@Point10_Notes = '', Null, @Point10_Notes)),
		Point11_Notes = (IIF(@Point11_Notes = '', Null, @Point11_Notes)),
		Point12_Notes = (IIF(@Point12_Notes = '', Null, @Point12_Notes)),

		Point1_EffortID = (IIF(@Point1_EffortID <= 0, Null, @Point1_EffortID)),
		Point2_EffortID = (IIF(@Point2_EffortID <= 0, Null, @Point2_EffortID)),
		Point3_EffortID = (IIF(@Point3_EffortID <= 0, Null, @Point3_EffortID)),
		Point4_EffortID = (IIF(@Point4_EffortID <= 0, Null, @Point4_EffortID)),
		Point5_EffortID = (IIF(@Point5_EffortID <= 0, Null, @Point5_EffortID)),
		Point6_EffortID = (IIF(@Point6_EffortID <= 0, Null, @Point6_EffortID)),
		Point7_EffortID = (IIF(@Point7_EffortID <= 0, Null, @Point7_EffortID)),
		Point8_EffortID = (IIF(@Point8_EffortID <= 0, Null, @Point8_EffortID)),
		Point9_EffortID = (IIF(@Point9_EffortID <= 0, Null, @Point9_EffortID)),
		Point10_EffortID = (IIF(@Point10_EffortID <= 0, Null, @Point10_EffortID)),
		Point11_EffortID = (IIF(@Point11_EffortID <= 0, Null, @Point11_EffortID)),
		Point12_EffortID = (IIF(@Point12_EffortID <= 0, Null, @Point12_EffortID)),

		OGHighestPreviousGCSEGrade = @StartGradeGrade, 
		OGHighestPreviousGCSEPoints = @StartGradePoints, 
		OGHighestPreviousGCSEGradeSavedBy =  @StartGradeUserName,    
		OGHighestPreviousGCSEGradeSavedWhen  = @StartGradeDateTime
	WHERE
		ID = @InYearGradeID;


	-- ***********************************************************
	--  Populate EnrolmentWebSave
	-- ***********************************************************
	DELETE FROM EngMatEnrolmentWebSave WHERE EngMatEnrolmentID = @EnrolmentID AND UserName = @UserName;
	INSERT INTO EngMatEnrolmentWebSave (EngMatEnrolmentID, UserName) VALUES (@EnrolmentID, @UserName);


	-- ***********************************************************
	--  Regenerate the EngMat data 
	--  We now do this separately to speed up the whole process i.e. when multiple Student changes
	-- ***********************************************************
	--EXEC sp_EngMat_Populate_Summary_Tables @EngMatType, 1, @UserName


	-- ***********************************************************
	--  Construct the RowDesc e.g. 22/23,Firstname_450882 450882,21271W
	-- ***********************************************************
	SET @RowDescription = (@AcademicYearID + ',' + @Forenames + ' ' + @Surname + ',' + @CourseCode);


	-- ***********************************************************
	--  Create Audit entries for the changed Grade fields
	-- ***********************************************************
	IF @Point1_Grade != @Point1_Grade_Existing BEGIN
	    IF IsNull(@Point1_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point1_Grade', null, null, @Point1_Grade_Existing, @Point1_Grade)
	END
	IF @Point2_Grade != @Point2_Grade_Existing BEGIN
	    IF IsNull(@Point2_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point2_Grade', null, null, @Point2_Grade_Existing, @Point2_Grade)
	END
	IF @Point3_Grade != @Point3_Grade_Existing BEGIN
	    IF IsNull(@Point3_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point3_Grade', null, null, @Point3_Grade_Existing, @Point3_Grade)
	END
	IF @Point4_Grade != @Point4_Grade_Existing BEGIN
	    IF IsNull(@Point4_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point4_Grade', null, null, @Point4_Grade_Existing, @Point4_Grade)
	END
	IF @Point5_Grade != @Point5_Grade_Existing BEGIN
	    IF IsNull(@Point5_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point5_Grade', null, null, @Point5_Grade_Existing, @Point5_Grade)
	END
	IF @Point6_Grade != @Point6_Grade_Existing BEGIN
	    IF IsNull(@Point6_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point6_Grade', null, null, @Point6_Grade_Existing, @Point6_Grade)
	END
	IF @Point7_Grade != @Point7_Grade_Existing BEGIN
	    IF IsNull(@Point7_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point7_Grade', null, null, @Point7_Grade_Existing, @Point7_Grade)
	END
	IF @Point8_Grade != @Point8_Grade_Existing BEGIN
	    IF IsNull(@Point8_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point8_Grade', null, null, @Point8_Grade_Existing, @Point8_Grade)
	END
	IF @Point9_Grade != @Point9_Grade_Existing BEGIN
	    IF IsNull(@Point9_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point9_Grade', null, null, @Point9_Grade_Existing, @Point9_Grade)
	END
	IF @Point10_Grade != @Point10_Grade_Existing BEGIN
	    IF IsNull(@Point10_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point10_Grade', null, null, @Point10_Grade_Existing, @Point10_Grade)
	END
	IF @Point11_Grade != @Point11_Grade_Existing BEGIN
	    IF IsNull(@Point11_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point11_Grade', null, null, @Point11_Grade_Existing, @Point11_Grade)
	END
	IF @Point12_Grade != @Point12_Grade_Existing BEGIN
	    IF IsNull(@Point12_Grade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point12_Grade', null, null, @Point12_Grade_Existing, @Point12_Grade)
	END


	-- ***********************************************************
	--   Create Audit entries for the changed Notes fields
	-- ***********************************************************
	IF @Point1_Notes != @Point1_Notes_Existing BEGIN
	    IF IsNull(@Point1_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point1_Notes', null, null, @Point1_Notes_Existing, @Point1_Notes)
	END
	IF @Point2_Notes != @Point2_Notes_Existing BEGIN
	    IF IsNull(@Point2_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point2_Notes', null, null, @Point2_Notes_Existing, @Point2_Notes)
	END
	IF @Point3_Notes != @Point3_Notes_Existing BEGIN
	    IF IsNull(@Point3_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point3_Notes', null, null, @Point3_Notes_Existing, @Point3_Notes)
	END
	IF @Point4_Notes != @Point4_Notes_Existing BEGIN
	    IF IsNull(@Point4_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point4_Notes', null, null, @Point4_Notes_Existing, @Point4_Notes)
	END
	IF @Point5_Notes != @Point5_Notes_Existing BEGIN
	    IF IsNull(@Point5_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point5_Notes', null, null, @Point5_Notes_Existing, @Point5_Notes)
	END
	IF @Point6_Notes != @Point6_Notes_Existing BEGIN
	    IF IsNull(@Point6_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point6_Notes', null, null, @Point6_Notes_Existing, @Point6_Notes)
	END
	IF @Point7_Notes != @Point7_Notes_Existing BEGIN
	    IF IsNull(@Point7_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point7_Notes', null, null, @Point7_Notes_Existing, @Point7_Notes)
	END
	IF @Point8_Notes != @Point8_Notes_Existing BEGIN
	    IF IsNull(@Point8_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point8_Notes', null, null, @Point8_Notes_Existing, @Point8_Notes)
	END
	IF @Point9_Notes != @Point9_Notes_Existing BEGIN
	    IF IsNull(@Point9_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point9_Notes', null, null, @Point9_Notes_Existing, @Point9_Notes)
	END
	IF @Point10_Notes != @Point10_Notes_Existing BEGIN
	    IF IsNull(@Point10_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point10_Notes', null, null, @Point10_Notes_Existing, @Point10_Notes)
	END
	IF @Point11_Notes != @Point11_Notes_Existing BEGIN
	    IF IsNull(@Point11_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point11_Notes', null, null, @Point11_Notes_Existing, @Point11_Notes)
	END
	IF @Point12_Notes != @Point12_Notes_Existing BEGIN
	    IF IsNull(@Point12_Notes_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point12_Notes', null, null, @Point12_Notes_Existing, @Point12_Notes)
	END


	-- ***********************************************************
	--  Create Audit entries for the changed EffortGrade fields
	-- ***********************************************************
	IF @Point1_EffortDescription_New != @Point1_EffortDescription_Existing BEGIN
	    IF IsNull(@Point1_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point1_EffortID', null, null, @Point1_EffortDescription_Existing, @Point1_EffortDescription_New)
	END
	IF @Point2_EffortDescription_New != @Point2_EffortDescription_Existing BEGIN
	    IF IsNull(@Point2_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point2_EffortID', null, null, @Point2_EffortDescription_Existing, @Point2_EffortDescription_New)
	END
	IF @Point3_EffortDescription_New != @Point3_EffortDescription_Existing BEGIN
	    IF IsNull(@Point3_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point3_EffortID', null, null, @Point3_EffortDescription_Existing, @Point3_EffortDescription_New)
	END
	IF @Point4_EffortDescription_New != @Point4_EffortDescription_Existing BEGIN
	    IF IsNull(@Point4_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point4_EffortID', null, null, @Point4_EffortDescription_Existing, @Point4_EffortDescription_New)
	END
	IF @Point5_EffortDescription_New != @Point5_EffortDescription_Existing BEGIN
	    IF IsNull(@Point5_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point5_EffortID', null, null, @Point5_EffortDescription_Existing, @Point5_EffortDescription_New)
	END
	IF @Point6_EffortDescription_New != @Point6_EffortDescription_Existing BEGIN
	    IF IsNull(@Point6_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point6_EffortID', null, null, @Point6_EffortDescription_Existing, @Point6_EffortDescription_New)
	END
	IF @Point7_EffortDescription_New != @Point7_EffortDescription_Existing BEGIN
	    IF IsNull(@Point7_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point7_EffortID', null, null, @Point7_EffortDescription_Existing, @Point7_EffortDescription_New)
	END
	IF @Point8_EffortDescription_New != @Point8_EffortDescription_Existing BEGIN
	    IF IsNull(@Point8_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point8_EffortID', null, null, @Point8_EffortDescription_Existing, @Point8_EffortDescription_New)
	END
	IF @Point9_EffortDescription_New != @Point9_EffortDescription_Existing BEGIN
	    IF IsNull(@Point9_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point9_EffortID', null, null, @Point9_EffortDescription_Existing, @Point9_EffortDescription_New)
	END
	IF @Point10_EffortDescription_New != @Point10_EffortDescription_Existing BEGIN
	    IF IsNull(@Point10_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point10_EffortID', null, null, @Point10_EffortDescription_Existing, @Point10_EffortDescription_New)
	END
	IF @Point11_EffortDescription_New != @Point11_EffortDescription_Existing BEGIN
	    IF IsNull(@Point11_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point11_EffortID', null, null, @Point11_EffortDescription_Existing, @Point11_EffortDescription_New)
	END
	IF @Point12_EffortDescription_New != @Point12_EffortDescription_Existing BEGIN
	    IF IsNull(@Point12_EffortDescription_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'Point12_EffortID', null, null, @Point12_EffortDescription_Existing, @Point12_EffortDescription_New)
	END


	-- ***********************************************************
	--  Create Audit entries for the changed PersonalTargetGrade fields
	-- ***********************************************************
	IF @PersonalTargetGrade != @PersonalTargetGrade_Existing BEGIN
	    IF IsNull(@PersonalTargetGrade_Existing, '') = ''  SET @TypeOfChange = 'INSERT'  ELSE  SET @TypeOfChange = 'UPDATE';
		INSERT INTO AuditTrail 
			(AuditDate, AuditBy, AuditChange, TableName, RowID, RowDescription, FieldName, OldValue, NewValue, OldValueDescription, NewValueDescription)
		VALUES
			(GetDate(), @UserName, @TypeOfChange, 'EngMatInYearGrade', @InYearGradeID, @RowDescription, 'PersonalTargetGrade', null, null, @PersonalTargetGrade_Existing, @PersonalTargetGrade)
	END	


	-- ***********************************************************
	--  Return 1 for success
	-- ***********************************************************
	SELECT 1;

END
ELSE
BEGIN
    --ID doesnt exist so return -1
	SELECT -1;
END

